Currently, information reporting systems use predefined query techniques to hide the complexity of Structured Query Language (SQL) and relational databases. Typically, members of Management Information System (MIS) staff build a database solution by creating user-dedicated tables, relational views or predefined SQL queries. These are then made available to users by means of menus or similar techniques. In such systems, if end-users want to change the purpose of a query they usually ask the MIS staff to program another query. Alternatively the user may program the SQL query or command themselves. However, the syntax of non-procedural languages (in particular SQL) is complex, and typically, the data structure is not expressed in terms of the users' everyday work. Relational databases store information as well as metadata (i.e. data describing the data organisation), such as tables, columns, keys, indices, and their structure and design. Although suited to the overall needs of the customer organisation, these databases will likely contain much that is not of interest to a particular user. In addition, although a query may be syntactically correct, its results may not be what are expected, due to the inherent complexity of a large-scale database. Indeed, the results may be totally meaningless.
For these and other reasons modeling tools are often used that allow conceptual modeling of databases. These tools provide a layer on top of the database, and allow the underlying database to be accessed in terms that are more relevant to a particular end application. Such modeling tools include “Impromptu”, “Transformer”, and “Architect” by Cognos Incorporated.
U.S. Pat. No. 6,662,188 “Metadata model”, Rasmussen, et al. issued Dec. 9, 2003, hereby incorporated by reference, describes a three level abstraction model for use in such environments. This is briefly described with reference to FIG. 2.
The lowest level in the database abstraction is the internal level 1. In the internal level 1, the database is viewed as a collection of files organized according to an internal data organization. The internal data organization may be anyone of several possible internal data organizations, such as B+-tree data organization and relational data organization.
The middle level in the database abstraction is the conceptual level 2. In the conceptual level 2, the database is viewed at an abstract level. The user of the conceptual level 2 is thus shielded from the internal storage details of the database viewed at the internal level 1.
The highest level in the database abstraction is the external level 3. In the external level 3, each group of users has their own perception or view of the database. Each view is derived from the conceptual level 2 and is designed to meet the needs of a particular group of users. To ensure privacy and security of data, each group of users only has access to the data specified by its particular view for the group.
The mapping between the three levels of database abstraction is the task of the Database Management System (DBMS). When the data structure or file organization of the database is changed, the internal level 1 is also changed. When changes to the internal level 1 do not affect the conceptual level 2 and en level 3, the DBMS is said to provide for physical data independence. When changes to the conceptual level 2 do not affect the external level 3, the DBMS is said to provide for logical data independence.
Typical DBMSs use a data model to describe the data and its structure, data relationships, and data constraints in the database. Some data models provide a set of operators that are used to update and query the database. DBMSs may be classified as either record-based systems or object-based systems. Both types of DBMSs use a data model to describe databases at the conceptual level 2 and external level 3.
Data models may also be called metadata models as they store metadata, i.e., data about (or describing) data in databases. Three main existing data models used in record based systems are the relational model, the network model and the hierarchical model.
In the relational model, data is represented as a collection of relations. To a large extent, each relation can be thought of as a table. A typical relational database contains catalogues, each catalogue contains schemas, and each schema contains tables, views, stored procedures and synonyms. Each table has columns, keys and indexes. A key is a set of columns whose composite value is distinct for all rows. No proper subset of the key is allowed to have this property. A table may have several possible keys. Data at the conceptual level 2 is represented as a collection of interrelated tables. The tables are normalized so as to minimize data redundancy and update anomalies. The relational model is a logical data structure based on a set of tables having common keys that allow the relationships between data items to be defined without considering the physical database organization.
A known high level conceptual data model is the Entity-Relationship (ER) model. In an ER model, data is described as entities, attributes and relationships. An entity is any about which data can be stored. Each entity has a set of properties, called attributes, which describe the entity. A relationship is an association between entities. For example, a professor entity may be described by its name, age, and salary gad can be associated with a department entity by the relationship “works for”. Existing information systems use business intelligence tools or client applications that provide data warehousing and business decision making and data analysis support services using a data model. In a typical information system, a business intelligence tool is conceptually provided on the top of a data model, and underneath of the data model is a database. The data model of existing information systems typically has layers corresponding to the external level 3 and the internal level 1.
Existing data models are used for the conceptual design of databases. When a system designer constructs an information system, the designer starts from a higher abstraction level 3 and moves down to a lower abstraction level 1, as symbolized in FIG. 2 by arrows. That is, the system designer first performs logical design. At the logical design stage, the designer considers entities of interest to the system users and identifies, at an abstract level, information to be recorded about entities. The designer the determines the conceptual scheme, i.e., the external level 3 and/or conceptual level 2 of a data model. After the logical design is completed, the designer next performs physical design. At the physical design stage, the designer decides how the data is to be represented in a database. The designer then creates the corresponding storage scheme, i.e., the structure of a database, and provides mapping between the internal level 1 of the data model and the database.
Existing business intelligence tools typically provide a different paradigm for retrieving and delivering information from a database. Accordingly, it is difficult to share information in the database among different business intelligence tools.
It is common that in a single organization, each group of users has its own established information system that uses its corresponding database. Thus, a single organization often has multiple databases. Those databases often contain certain types of information that are useful for multiple groups of users. Such types of information may include information about business concepts, data retrieval, and user limits and privileges. However, each information system is designed and constructed in accordance with specific needs of the group, and may use a different business intelligence tool from others. These differences in the information systems and business intelligence tools used do not allow sharing the information already existing in the databases among multiple groups of users.
The previous invention, “Metadata Model—Rasmussen, et al”, describes ai data model or metadata model that realizes the three abstraction levels and provides information that can be shared by multiple users who use those different business intelligence tools or client applications. It also provides for a metadata model to contain model objects representing one or more data sources. The data sources contain tables having columns. The metadata model comprises a data access layer, a business layer and a package layer. The data access layer contains data access model objects. The data access model objects include table objects that describe definitions of the tables contained in the data sources, and column objects that describe definitions of the columns of the tables contained in the data sources. The business layer contains business model objects. The business model objects include entities that are constructed based on the table objects in the data access layer, and attributes that are constructed based on the column objects in the data access layer. The package layer contains package model objects. The package model objects include a package model object that references a subset of the business model objects.
What is needed is a way to reduce the apparent complexity and inherent learning barrier to the use of the earlier invention.